<?php
defined('IN_YuLin') || exit('NO PERMIT!');
/**
 * 获取用户所有的订单信息
 * @param array $param: 筛选查询条件
 * @param boolen $all:  默认七天，查询所有虚额外指定
 */
function getOrderData ($param, $field='', $all=false){
    global $db;
    $lottIdKey = getLottIdKey($db);
    $mainTab   = Table('lottery_order_main');
    $itemTab   = Table('lottery_order_item');
    $perTab    = Table('lottery_k3');
    $field  = $field ? $field : 'main.id, main.uid, main.order_no, main.`code`, main.amount, main.`status`, main.platform, main.prize, main.createtime, main.`code`,
                            	item.id AS item_id, item.`period_id`,
                            	per.`code` as kj_code, per.`period`,
                                lott.`title` as lottTitle
                            ';
    $where  = ' WHERE 1 ';
    if ($param['uid']) {
        $where .= ' AND main.`uid` = ' . $param['uid'];
    }
    if (!$all){
        $where .= ' AND main.`createtime` > ' . strtotime(date('Y-m-d', strtotime("-7 day") ));
    }
    $getOrderMainSql = 'SELECT ' . $field . '
                    FROM ' . $mainTab . ' AS main 
                    LEFT JOIN ' . $itemTab . ' AS item
                    ON main.id = item.`o_id`
                    LEFT JOIN ' . $perTab . ' AS per
                    ON item.`period_id` = per.`id`
                    LEFT JOIN yulin_lottery AS lott
                    ON lott.`id` = main.`lott_id`
                    ' . $where . ' GROUP BY item.`period_id` ORDER BY main.`createtime` DESC LIMIT 100';
    $orderMain  = $db->getall($getOrderMainSql);
    // 订单信息整理为idkey的形式
    $orderMainIdKey = array();
    foreach ($orderMain as $key => $value){
        // 时间格式化
        $value['date_createtime'] = date('Y-m-d H:i:s', $value['createtime']);
        $orderMainIdKey[$value['id']] = $value;
    }
    $oid        = 'item.o_id IN (0';
    foreach ($orderMain as $mk => $mv){
        $oid   .= ',' . $mv['id'];
    }
    $oid       .= ')';
    $itemSql    = 'SELECT item.id, item.o_id, item.period_id, item.lott_id, item.en_play, item.cn_play, item.betting_str, item.betting_no, item.num, item.money_unit, item.money, item.odds, item.prize, item.winner, item.chase, item.`status`, item.winner_end, item.prize_status,
                    	qihao.`period`, qihao.`code`
                    FROM ' . Table('lottery_order_item') . ' AS item
                    LEFT JOIN ' . Table('lottery_k3') . ' AS qihao
                    ON item.`period_id` = qihao.`id`
                    WHERE ' . $oid . ' AND item.chase = 0 ORDER BY item.id DESC';
    $orderItem  = $db->getall($itemSql);
    // 将orderItem信息整理为期号为Key的信息
    $orderItemOidKey = array();
    foreach ($orderItem as $k => $v){
        if(!$v['period']) continue;
        $orderItemOidKey[$v['o_id']]['period']       = $v['period'];
        $orderItemOidKey[$v['o_id']]['betting_str']  = $v['betting_str'];
        $orderItemOidKey[$v['o_id']]['en_play']      = $v['en_play'];
        $orderItemOidKey[$v['o_id']]['cn_play']      = $v['cn_play'];
        $orderItemOidKey[$v['o_id']]['bet_money']   += $v['money'];
        $orderItemOidKey[$v['o_id']]['kj_code']      = $v['code'];
        $orderItemOidKey[$v['o_id']]['prize_amount'] += $v['prize'];
        $orderItemOidKey[$v['o_id']]['lottTitle']    = $lottIdKey[$v['lott_id']]['title'];
        $orderItemOidKey[$v['o_id']]['o_id']         = $v['o_id'];
        $orderItemOidKey[$v['o_id']]['chase']        = $v['chase'];
        $orderItemOidKey[$v['o_id']]['lott_id']      = $v['lott_id'];
        $orderItemOidKey[$v['o_id']]['item_id']      = $v['id'];
        $orderItemOidKey[$v['o_id']]['winner_status']= 0;
        if($orderItemOidKey[$v['o_id']]['winner_status'] < $v['winner']){
            // 只要有一个投注项中奖， 则当前期号投注算中奖
            $orderItemOidKey[$v['o_id']]['winner_status']= $v['winner'];
        }
           
        $orderItemOidKey[$v['o_id']]['item'][] = $v;
    }
    krsort($orderItemOidKey);
    return array(
        'orderMain' => $orderMainIdKey,  // 前端少写五个字符
        'orderItem' => $orderItemOidKey,
        'lottIdKey' => $lottIdKey,
        'itemCount' => count($orderItemOidKey),
        'pageSuport'=> array(
            'today'     => strtotime(date('Y-m-d')),
            'yesterday' => strtotime(date('Y-m-d', strtotime("-1 day"))),
            'week'       => strtotime(date('Y-m-d', strtotime("-7 day"))),
        )
    );
}

/**
 * 获取投注项信息
 * @param array $param
 */
function getOrderItemInfo($param, $all=false){
    global $db;
    // 获取当前查看item的所有相关信息
    $sql = 'SELECT main.`order_no`, main.`createtime`, main.`amount`, main.`code` as main_code,
            	per.`code` as open_code, per.`period`,
                lott.title as lott_title, 
            	item.`betting_no`, item.`betting_str`, item.`cn_play`, item.`num`, item.`money`, item.`money_unit`, item.`prize`, item.`winner`, item.`period_id`
            FROM ' . Table('lottery_order_item') . ' AS item
            LEFT JOIN ' . Table('lottery_order_main') . ' AS main
            ON main.`id` = item.`o_id`
            LEFT JOIN ' . Table('lottery') . ' AS lott
            ON item.`lott_id` = lott.`id`
            LEFT JOIN ' . Table('lottery_k3') . ' AS per
            ON per.`id` = item.`period_id`
            WHERE item.`id` = ' . $param['oid'];
    
    $orderView   = $db->getrow($sql);
    $orderView['re_createtime'] = date('Y-m-d H:i:s', $orderView['createtime']);
    $orderInfoSql= 'SELECT order_no, createtime, amount, `code`';
    $itemTab     = Table('lottery_order_item');
    $itemInfoSql = 'SELECT o_id FROM ' . $itemTab . ' WHERE id = ' . $param['oid'];
    $itemInfo    = $db->getrow($itemInfoSql);
    $allItemSql  = 'SELECT id, period_id, lott_id, en_play, cn_play, betting_str, betting_no, num, multiple, money_unit, eo_money, money, odds,  
                    prize, winner, chase, status, winner_end, prize_status
                    FROM ' . $itemTab . ' WHERE o_id = ' . $itemInfo['o_id'] . ' ORDER BY winner';
    $allItem     = $db->getall($allItemSql);
    $betPlayData = array();
    foreach ($allItem as $ak => $av){
        $tmp = array(
            'cn_play'     => $av['cn_play'],
            'betting_str' => $av['betting_str'],
            'num'         => $betPlayData[$av['en_play']]['num'] + $av['num'],
            'multiple'    => $av['multiple'],
            'money_unit'  => $av['money_unit'],
            'money'       => $betPlayData[$av['en_play']]['money'] + $av['money'],
            'prize'       => $betPlayData[$av['en_play']]['prize'] + $av['prize'],
            'winner'      => $av['winner']
        );
        $betPlayData[$av['en_play']] = $tmp;
    }
    
    $userInfo  = array(
        'userName' => $_SESSION['userlast']['username'],
    );
    return array('orderView' => $orderView, 'betPlayData' => $betPlayData, 'userInfo' => $userInfo);
}

function getChaseOrderData ($param, $page='', $all=''){
    global $db;
    $mainTab = Table('lottery_order_main');
    $itemTab = Table('lottery_order_item');
    $lottIdKey = getLottIdKey($db);
    $where     = ' WHERE 1 ';
    if ($param['uid']) {
        $where .= ' AND main.`uid` = ' . $param['uid'];
    }
    if (!$all){
        $where .= ' AND main.`createtime` > ' . strtotime(date('Y-m-d', strtotime("-7 day") ));
    }
    $where .= ' AND main.`is_chase` = 1';
    // 分页信息
    $pageCount = 0;
    $showNums  = 20;
    $limit     = $showNums * ($page['np'] - 1) . ',' . ($showNums * $page['np']);
    
    // 查询总记录数
    $pageSql   = 'SELECT COUNT(main.id) as cnt FROM ' . $mainTab . ' AS main 
                  ' . $where;
    $pageCount = $db->getrow($pageSql);
    $pageCount = intval($pageCount['cnt'] / $showNums);
    $pageCount+= ($pageCount % $showNums) ? '1' : 0;
    // 查询订单主表基础信息
    $mainListSql = 'SELECT main.id, main.lott_id, main.uid, main.order_no, main.`code`, main.amount, main.createtime FROM ' . $mainTab . ' AS main 
                  ' . $where . ' ORDER BY id DESC LIMIT ' . $limit;
    $mainList    = $db->getall($mainListSql);
    
    $mainIdKey   = array();
    $where  = ' WHERE item.o_id IN (0';
    foreach ($mainList as $k => $v){
        $v['winner_amount']  = 0;
        $v['open_times']     = 0;
        $v['was_bet_money']  = 0;
        $v['item_times']     = 0;
        $v['date_createtime']= date('Y-m-d H:i:s', $v['createtime']);
        $mainIdKey[$v['id']] = $v;
        $where .= ',' . $v['id'];
    }
    $where .= ')';
    krsort($mainIdKey);
    // 获取追号订单以及部分追号信息
    $itemSql    = 'SELECT item.id, item.o_id, item.`betting_no`, item.`betting_str`, item.`cn_play`, item.`num`, item.`money`, item.`money_unit`, item.`prize`, item.`winner`, item.`period_id`,
                per.`code`, per.`period`
                FROM ' . $itemTab . ' AS item
                LEFT JOIN ' . Table('lottery_k3') . ' AS per
                ON item.`period_id` = per.`id`
                ' . $where . ' ORDER BY id DESC';
    $itemList   = $db->getall($itemSql);
    // item表信息o_idKey化
    $itemOidKey = array();
    foreach ($itemList as $ik => $iv){
        if($iv['winner'] == 2){
            // 给主订单表计算中奖金额
            $mainIdKey[$iv['o_id']]['winner_amount'] += $iv['prize'];
        }
        if ($iv['winner'] > 0){
            // 已追奖期数累加
            $mainIdKey[$iv['o_id']]['open_times']    += 1;
            $mainIdKey[$iv['o_id']]['was_bet_money'] += $iv['money'];
        }
        $itemOidKey[$iv['o_id']][] = $iv;
        $mainIdKey[$iv['o_id']]['item_times']  += 1;
    }
    
    
    
    $pageData  = array(
        'mainList' => $mainIdKey,
        'itemList' => $itemOidKey,
        'count'    => $pageCount,
        'np'       => $page['np']
    );
    return array(
        'data'       => $pageData,
        'lottIdKey'  => $lottIdKey,
        'pageSuport' => array(
            'today'     => strtotime(date('Y-m-d')),
            'yesterday' => strtotime(date('Y-m-d', strtotime("-1 day"))),
            'week'       => strtotime(date('Y-m-d', strtotime("-7 day"))),
        )
    );
}

/**
 * 获取追号详细
 * @param unknown $param
 */
function getChaseDetailInfo($param){
    global $db;
    $sql = 'SELECT main.`order_no`, main.`createtime`, main.`amount`, main.`lott_id`,
            	per.`code`, per.`period`, lott.title as lott_title,
            	item.`id`, item.`betting_no`, item.`betting_str`, item.`cn_play`, item.`num`, item.`money`, item.`money_unit`, item.`prize`, item.`winner`, item.`period_id`, item.`winner_end`
            FROM ' . Table('lottery_order_main') . ' AS main
            LEFT JOIN ' . Table('lottery_order_item') . ' AS item
            ON main.`id` = item.`o_id`
            LEFT JOIN ' . Table('lottery_k3') . ' AS per
            ON item.`period_id` = per.`id`
            LEFT JOIN ' . Table('lottery') . ' as lott
            on lott.id = main.lott_id
            WHERE main.`id` = ' . $param['oid'];
    $orderWithItem = $db->getall($sql);
    $orderInfo     = array(
        'id'              => $param['oid'],
        'order_no'        => $orderWithItem[0]['order_no'],
        'amount'          => $orderWithItem[0]['amount'],
        'date_createtime' => date('Y-m-d H:i:s', $orderWithItem[0]['createtime']),
        'lott_id'         => $orderWithItem[0]['lott_id'],
        'bet_str'         => $orderWithItem[0]['betting_str'],
        'bet_no'          => $orderWithItem[0]['betting_no'],
        'lott_title'      => $orderWithItem[0]['lott_title'],
        'cn_play'         => $orderWithItem[0]['cn_play'],
        'winner_end'      => $orderWithItem[0]['winner_end'],
        'money_unit'      => $orderWithItem[0]['money_unit'],
    );
    $open_times           = 0;
    $qishu_arr            = array();
    $zhuihao_amount       = 0;
    $winner_amount        = 0;
    foreach ($orderWithItem as $k => $v ){
        $tmp = array(
            'peroid_id'    => $v['peroid_id'],
            'peroid'       => $v['peroid'],
            'item_bet_no'  => $v['betting_no'],
            'item_bet_num' => $v['num'],
            'item_cn_play' => $v['cn_play'],
            'item_en_play' => $v['en_play'],
            'item_money'   => $v['money'],
            'item_unit'    => $v['money_unit'],
            'item_prize'   => $v['prize'],
            'item_winner'  => $v['winner'],
            'item_id'      => $v['id'],
        );
        if ($v['code']){
            $open_times++;
        }
        if($v['winner'] > 0){
            $zhuihao_amount += $v['money'];
        }
        if($v['winner'] == 2){
            $winner_amount += $v['money'];
        }
        
        if(!in_array($v['period'], $qishu_arr)) array_push($qishu_arr, $v['period']);
        $orderInfo['item'][$v['period']]['open_code'] = $v['code'];
        $orderInfo['item'][$v['period']]['bet'][]     = $tmp;
    }
    $orderInfo['open_tims']        = $open_times;
    $orderInfo['qishu']            = count($qishu_arr);
    $orderInfo['zhuihao_amount']   = $zhuihao_amount;
    $orderInfo['winner_amount']    = $winner_amount;
    $orderInfo['every_qishu_nums'] = count($orderInfo['item'][$v['period']]['bet']);
    
    return array('orderInfo' =>$orderInfo);
}

/**
 * 获取追号记录中单个期号的信息（追号记录->某个追号信息->单个期号）
 * @param array $param
 */
function getChasePeriodInfo($param){

}

/**
 * 今日盈亏
 */
function profitAndLoss ($param){
    global $db;
    // 用户信息
    $userInfoSql  = 'SELECT username, money FROM ' . Table('user') . ' WHERE id = ' . $param['uid'];
    $userInfo     = $db->getrow($userInfoSql);
    
    // 用户今日投注支出
    $todayTime    = strtotime(date('Y-m-d', time()));
    $orderSql     = 'SELECT SUM(amount) as cre_order_amount FROM yulin_lottery_order_main WHERE uid = ' . $param['uid'] . ' AND createtime > ' . $todayTime;
    $order        = $db->getrow($orderSql);
    // 投注盈利
    $winnerSql    = 'SELECT SUM(item.`prize`) as winner_amount FROM ' . Table('lottery_order_main') . ' AS main
                    LEFT JOIN ' . Table('lottery_order_item') . ' AS item
                    ON main.`id` = item.`o_id`
                    WHERE uid = ' . $param['uid'] . ' AND main.`createtime` > ' . $todayTime;
    $winner       = $db->getrow($winnerSql);
    // 充值金额
    $rechargeSql  = 'SELECT SUM(money) as recharge_amount FROM ' . Table('user_money_log') . ' 
        WHERE uid = ' . $param['uid'] . ' AND dateline > ' . $todayTime . ' AND tid = "recharge"'; // 固定写死的标识
    $recharge     = $db->getrow($rechargeSql);
    // 提现金额
    $postalSql    = 'SELECT SUM(money) as postal_amount FROM ' . Table('user_money_log') . '
        WHERE uid = ' . $param['uid'] . ' AND dateline > ' . $todayTime . ' AND tid = "postal"';  // 固定写死的标识
    $postal       = $db->getrow($postalSql);
    return array(
        'userInfo' => $userInfo,
        'order'    => $order,
        'winner'   => $winner,
        'recharge' => $recharge,
        'postal'   => $postal,
    );
}

/**
 * @param array $param
 */
function getMoneyLog($param){
    global $db;
    $weekTime     = strtotime(date('Y-m-d', strtotime("-7 day") ));
    $where        = '';
    if ($param['isagent']){
        // 是代理查询
        if($param['appoint_uid']){
            // 指定了下级
            $where        = ' WHERE uid = ' . $param['appoint_uid'];
        }else{
            // 没有指定下级， 查询所有下级
            $sql         = 'SELECT id FROM ' . Table('user_invita_code') . ' WHERE uid = ' . $param['uid'];
            $allCodeData = $db->getall($sql, 'id');
            $codeIdStr   = '(' . array_keys($allCodeData) . ')';
            $sql = 'SELECT id FROM ' . Table('user') . ' AS u
                    LEFT JOIN ' . Table('user_info') . ' AS info
                    ON u.`id` = info.`uid`
                    WHERE info.`invita_code_id` IN ' . $codeIdStr;
            $userData = $db->getall($sql, 'id');
            $uidStr   = explode(',', array_keys($userData));
            $where    = 'WHERE uid IN (' . $uidStr . ')';
        }
    }else{
        $where        = ' WHERE uid = ' . $param['uid'];
    }
    $where       .= ' AND dateline > ' . $weekTime;
    $allLogSql    = 'SELECT id, tid, lkey, money, balance, freeze, total, balance_old, freeze_old, total_old, dateline, ip, notes FROM yulin_user_money_log AS lg ' . $where . ' ORDER BY id DESC limit 500';
    $moneyLogData = $db->getall($allLogSql);
    foreach ($moneyLogData as $k => $v){
        $moneyLogData[$k]['date_dateline'] = date('Y-m-d H:i:s', $v['dateline']);
        $tmp                               = explode('_', $v['lkey']);
        $moneyLogData[$k]['order_no']      = array_shift($tmp);
    }
    return array(
        'moneyLog'   => $moneyLogData,
        'pageSuport' => array(
            'today'     => strtotime(date('Y-m-d')),
            'yesterday' => strtotime(date('Y-m-d', strtotime("-1 day"))),
            'week'      => strtotime(date('Y-m-d', strtotime("-7 day"))),
        )
    );
}

/**
 * 获取投注页面右侧内容【当前彩票开奖信息，我的投注，中奖信息】
 * @param array $param
 */
function getBetPageRightData($param){
    global $db;
    // 历史开奖
    $historyOpenData = array();
    // 当前彩种信息
    $lottInfo        = '';
    // 查询7天以内的信息
    $dayLine         = strtotime(date('Ymd', strtotime('-7 day')));
    // 共用兼容性检查
    if($param['lott_id']){
        // 获取当前彩票的开奖信息（10条）
//         $openPeriodSql    = 'SELECT * FROM ' . Table('lottery_k3') . ' WHERE tid = ' . $param['lott_id'] . ' AND `code` != "" ORDER BY id DESC LIMIT 10';
        $openPeriodSql    = 'SELECT id, tid, period, starttime, stoptime, day, opentime, `code` FROM ' . Table('lottery_k3') . 
        ' WHERE tid = ' . $param['lott_id'] . ' AND stoptime < ' . time(). ' AND `code` != "" ORDER BY starttime DESC limit 10';
        $historyOpenData  = $db->getall($openPeriodSql);
        foreach ($historyOpenData as $k1 => $v1){
            $historyOpenData[$k1]['date_opentime'] = date('H:i:s', $v1['opentime']);
        }
    }
    
    // 获取用户的投注信息
    $mainTable        = Table('lottery_order_main');
    $itemTable        = Table('lottery_order_item');
    $perTable         = Table('lottery_k3');
    $userTable        = Table('user');
    $lottTable        = table('lottery');
    // 共用兼容性检查
    if($param['uid']){
        // 有界面会不需要指定彩种id
        if ($param['lott_id']) $where = ' AND main.`lott_id` = ' . $param['lott_id'] . ' AND main.createtime > ' . $dayLine;
        $myWinnerSql       = 'SELECT main.`order_no`, main.`id`, 
                            	item.`chase`, item.`money`, item.`prize`, item.`winner`,
                            	per.`period` 
                            FROM ' . $itemTable . ' AS item
                            LEFT JOIN ' . $perTable . ' AS per
                            ON item.`period_id` = per.`id`
                            LEFT JOIN ' . $mainTable . ' AS main
                            ON main.`id` = item.`o_id`
                            WHERE main.`uid` = ' . $param['uid'] . $where . ' ORDER BY main.`createtime` DESC LIMIT 5';
    }
    $myBetData     = $db->getall($myWinnerSql);
    
    // 中奖信息
    $limit            = 30;
    if(!$param['uid'] || !$param['lott_id']) $limit = 100;
    $allWinnerSql     = 'SELECT main.`order_no`, main.`id`,
                        	item.`money`, item.`prize`, item.`winner`,
                        	lott.`title` as lott_title,
                        	u.`username`, u.`head_pic`, u.`id` as uid, u.`nickname`
                        FROM ' . $itemTable . ' AS item
                        LEFT JOIN ' . $mainTable . ' AS main
                        ON main.`id` = item.`o_id`
                        LEFT JOIN ' . $lottTable . ' AS lott
                        ON lott.`id` = item.`lott_id`
                        LEFT JOIN ' . $userTable . ' AS u
                        ON main.`uid` = u.`id`
                        WHERE item.`winner` = 2 AND prize_status = 2 AND main.`createtime` > ' . $dayLine . ' ORDER BY main.`createtime` DESC LIMIT ' . $limit;
    $allWinnerData    = $db->getall($allWinnerSql);
    // 昨日盈利榜
    $getUpDayOrderSql = 'SELECT main.id, main.`uid`, u.`username` FROM yulin_lottery_order_main AS main 
                        LEFT JOIN yulin_user AS u
                        ON main.`uid` = u.`id`
                        WHERE main.`createtime` > ' . strtotime(date('Y-m-d', strtotime('-1 day'))) . ' AND main.`createtime` < ' . strtotime(date('Y-m-d')) . ' 
                        LIMIT 10';
    $upDayOrderData   = $db->getall($getUpDayOrderSql);
    // 获取昨日订单item信息并整理  ======================= 缺少数据， 昨天盈利数据不好取不好计算
    $orderIdKey       = array();
    $oidStr           = ' o_id IN(0';
    foreach ($upDayOrderData as $k2 => $v2){
        $oidStr      .= ',' . $v2['id'];
        $orderIdKey[$v2['id']] = $v2;
    }
    $oidStr          .= ')';
    $itemDataSql      = 'SELECT id, prize, o_id FROM yulin_lottery_order_item WHERE winner = 2 AND ' . $oidStr;
    $itemData         = $db->getall($itemDataSql);
    foreach ($itemData as $ik => $iv){
        $orderIdKey[$iv['o_id']]['winner_amount'] += $iv['prize'];
    }
    
    // 按照中奖金额排序
//     $yesterdayWinSql  = 'SELECT main.id, main.`order_no`, main.`lott_id`,
//                         	lott.`title` AS lott_title,
//                         	SUM(item.`prize`) AS winner_amount,
//                         	u.`username`, u.`head_pic`, u.`id` as uid
//                         FROM ' . $mainTable . ' AS main
//                         LEFT JOIN ' . $itemTable . ' AS item
//                         ON main.id = item.`o_id`
//                         LEFT JOIN ' . $lottTable . ' AS lott
//                         ON main.`lott_id` = lott.`id`
//                         LEFT JOIN ' . $userTable . ' AS u
//                         ON main.`uid` = u.`id`
//                         WHERE main.`createtime` > ' . strtotime(date('Y-m-d', strtotime('-1 day'))) . ' AND main.`createtime` < ' . strtotime(date('Y-m-d')) . ' AND item.`prize` > 0
//                         GROUP BY item.`o_id` ORDER BY winner_amount DESC LIMIT 30';
//     $yesterdayWinData = $db->getall($yesterdayWinSql);
    $sql = 'SELECT 
            u.`username`, u.`head_pic`,
            SUM(main.amount) AS winner_amount , main.`order_no`,
            lott.`title`, lott.`id`
            FROM ' . $mainTable . ' AS main
            LEFT JOIN ' . $userTable . ' AS u
            ON main.`uid` = u.`id`
            LEFT JOIN ' . $lottTable . ' AS lott
            ON main.`lott_id` = lott.`id`
            WHERE main.`createtime` > ' . strtotime(date('Y-m-d', strtotime('-1 day'))) . ' AND main.`createtime` < ' . strtotime(date('Y-m-d')) . ' AND main.prize > 0
            GROUP BY main.`uid` ORDER BY winner_amount DESC LIMIT 20';
    $yesterdayWinData  = $db->getall($sql);    
    // 获取当前彩种信息  
    if($param['lott_id']){
        $lottInfoSql      = 'SELECT id, title, ename, sname, ptid, periods, per_explain, cate_id FROM ' . $lottTable . ' WHERE id = ' . $param['lott_id'];
        $lottInfo         = $db->getrow($lottInfoSql);
    }
    return array(
        'history'  => $historyOpenData,
        'mybet'    => $myBetData,
        'allwin'   => $allWinnerData,
        'lottInfo' => $lottInfo,
        'updaywin' => $yesterdayWinData,
    );
}

/**
 * 获取所有彩种信息
 * @param unknown $db
 */
function getLottIdKey ($db){
    // 获取所有彩种
    $getLottSql = 'SELECT id, title, sname FROM ' . Table('lottery') . ' WHERE status = 1';
    $lottList   = $db->getall($getLottSql);
    
    // 彩票信息IdKey
    $lottIdKey  = array();
    foreach ($lottList as $lk => $lv){
        $lottIdKey[$lv['id']] = $lv;
    }
    return $lottIdKey;
}


// ===========================================================
function updateUserHeadPic($param){
    $res = array();
    if($param['pic'] < 0 && $param['pid'] > 102){
        $res['status'] = 'error';
        $res['msg']    = '图像信息设置错误';
        return $res;
    }
    global $db;
    $head_pic = 'static/images/head_pic/1_(' . $param['pid'] . ').jpg';
    $flag     = $db->exec('UPDATE '.Table('user').' SET head_pic = "'.$head_pic.'" WHERE id = ' . $param['uid']);
    if ($flag > 0) {
        $res['status'] = 'success';
        $res['msg']    = '设置成功';
    }else{
        $res['status'] = 'error';
        $res['msg']    = '网络错误';
    }
    return $res;
}

/**
 * 设置用户信息，有时间会考虑替代前面一个设置头像的方法
 * @param array $param： id参数放在最后面【重要】
 */
function updateUserInfo($param){
    global $db;
    $id       = array_pop($param);
    $tmp      = array();
    // 用户信息详细表
    $tmp2     = array();
    foreach ($param as $k => $v){
        if (in_array($k, array('truename', 'email', 'mobile', 'qq', 'wechat'))){
            array_push($tmp2, '`' . $k . '`="' . $v . '"');
        }else{
            array_push($tmp, '`' . $k . '`="' . $v . '"');
        }
    }
    if($tmp){
        // 修改用户基本信息
        $sql      = 'UPDATE ' . Table('user') . ' SET ' . implode(',', $tmp) . ' WHERE id = ' . $id;
        $flag     = $db->exec($sql);
    }
    if($tmp2){
        // 修改用户详细信息
        $sql      = 'UPDATE ' . Table('user_info') . ' SET ' . implode(',', $tmp2) . ' WHERE uid = ' . $id;
        $flag     = $db->exec($sql);
    }
    if ($flag > 0) {
        $res['status'] = 'success';
        $res['msg']    = '设置成功';
    }else{
        $res['status'] = 'error';
        $res['msg']    = '网络错误';
    }
    return $res;
}

/**
 * 用户提现页面信息载入
 * @param unknown $param
 */
function getUserPostalData($param){
    global $db;
    $id           = $param['uid'];
    $maxTimes     = 5;
    $moneyCashTab = Table('user_money_cash');
    $userBankTab  = Table('user_bank');
    $webBankTab   = Table('web_bank');
    // 查询用户是否在今天有过提现记录， 如果成功提现超过maxTimes次则不允许用户再次提现
    $getPostalSql = 'SELECT count(id) as cnt FROM ' . $moneyCashTab . ' WHERE uid = ' . $id . ' AND `status` = 1 AND dateline > ' . strtotime(date('Y-m-d'));
    $postalCnt    = $db->getrow($getPostalSql);
    // 获取用户设置的银行卡信息
    $userBankSql  = 'SELECT ub.id, ub.bank, ub.bankcard, ub.`truename`,
                    wb.`name`, wb.`tags` FROM ' . $userBankTab . ' AS ub
                    LEFT JOIN ' . $webBankTab . ' AS wb
                    ON ub.`bank` = wb.`id`
                    WHERE ub.`uid` = ' . $id . ' AND ub.`status` = 1';
    $userBankList = $db->getall($userBankSql);
    foreach ($userBankList as $k => $v){
        $userBankList[$k]['mini_bankcard'] = substr($v['bankcard'], -4);
        $userBankList[$k]['bankcard']      = $userBankList[$k]['mini_bankcard']; 
    }
    $res['bankList']     = $userBankList;
    $res['surplusTimes'] = ($maxTimes - $postalCnt['cnt']);
    return $res;
}

/**
 * 处理用户申请提现的请求
 */
function postApplyPostal($param){
    global $db;
    $res        = array('status' => 'error');
    $moneyCashTab = Table('user_money_cash');
    $ck_timesSql  = 'SELECT count(id) as cnt FROM ' . $moneyCashTab . ' WHERE uid = ' . $param['uid'] . ' AND `status` = 1 AND dateline > ' . strtotime(date('Y-m-d'));
    $check        = $db->getrow($ck_timesSql);
    if($check['cnt'] >= 5){
        $res['msg'] = '今天提现次数已达到上限';
        return $res;
    }
    // 获取提现的银行卡信息并验证安全码
    $getBankSql   = 'SELECT security, bank, bankcard, truename FROM ' . Table('user_bank') . ' WHERE id = ' . $param['bank_id'];
    $bankInfo     = $db->getrow($getBankSql);
    if(!$bankInfo){
        $res['msg'] = '银行卡信息获取失败';
        return $res;
    }
    // 验证安全码
    if (md5($param['security']) != $bankInfo['security']){
        $res['msg'] = '安全码不正确';
        return $res;
    }
    // 提交申请
    $weimiao   = microtime(true);
    $ins       = array(
        'uid'      => $param['uid'],
        'orderid'  => 'TX' . date('YmdHis') . str_pad(substr($weimiao, strpos($weimiao, '.') + 1), '4', 0),
        'money'    => $param['money'],
        'bank'     => $bankInfo['bank'],
        'bankcard' => $bankInfo['bankcard'],
        'truename' => $bankInfo['truename'],
        'notes'    => '',
        'dotime'   => '0',
        'ip'       => '',
        'status'   => '0',
        'dateline' => time(),
        'platform' => $param['platform'] ? $param['platform'] : 0,
    );
    $flag = $db->exec('INSERT INTO '.$moneyCashTab . CreateInsertSql($ins));
    if ($flag) {
        $res['status'] = 'success';
        $res['msg']    = '申请提交成功';
    }else{
        $res['msg']    = '申请失败，请刷新页面重试';
    }
    return $res;
}

/**
 * 获取用户的银行卡信息
 * @param array $param
 */
function getBankCardList($param){
    global $db;
    $bindBankTable = Table('user_bank');
    $webBank       = Table('web_bank');
    // 获取用户绑定的银行卡信息
    $sql      = 'SELECT ub.`id`, ub.`uid`, ub.`truename`, ub.`bank`, ub.`bankcard`, ub.`dateline`,
                        wb.`name`, wb.`tags`
                    FROM ' . $bindBankTable . ' AS ub
                    LEFT JOIN ' . $webBank . ' AS wb
                    ON ub.`bank` = wb.`id`
                    WHERE ub.`uid` = ' . $param['uid'] . ' AND ub.`status` = 1
                    ';
    $bindList = $db->getall($sql);
    // 银行卡号隐藏
    foreach ($bindList as $k => $v){
        $bindList[$k]['rebankcard'] = '************' . substr($v['bankcard'], -4);
        $retruename = '';
        for($i = 1; $i <= mb_strlen($v['truename']); $i++){
            if($i < mb_strlen($v['truename'])){
                $retruename .= '*';
            }
        }
        $retruename .= mb_substr($v['truename'], -1);
        $bindList[$k]['retruename'] = $retruename;
        $bindList[$k]['re_dateline'] = date('Y-m-d H:i:s', $v['dateline']);
    }
    return array('bindList' => $bindList);
}

/**
 * 解绑用户银行卡
 * @param array $param
 */
function unBindBankCart($param){
    global $db;
    $res = array(
        'status' => 'error',
        'msg'    => '网络错误，请重试'
    );
    if (!$param['uid'] || !$param['bid']) return $res;
    $sql      = 'UPDATE ' . Table('user_bank') . ' SET status = 0 WHERE id = ' . $param['bid'] . ' AND uid = ' . $param['uid'] ;
    $flag     = $db->exec($sql);
    if ($flag){
        $res['status'] = 'success';
        $res['msg']    = '解绑成功';
    }
    return $res;
}

/**
 * 用户绑定银行卡
 * @param array $param
 */
function userBindBankCard($param){
    global $db;
    $res = array(
        'status' => 'error',
        'msg'    => '网络错误，请重试'
    );
    $bindBankTable = Table('user_bank');
    $checkMaxSql     = 'SELECT COUNT(1) as cnt FROM ' . $bindBankTable . ' WHERE uid = ' . $param['uid'] . ' AND status = 1';
    $bindNum         = $db->getrow($checkMaxSql);
    if ($bindNum['cnt'] >= 5){
        $res['msg']  = '您已绑定了五张有效银行卡。';
        return $res;
    }
    $data              = (array)$param['param'];
    $data['uid']       = $param['uid'];
    $data['security']  = md5($data['security']);
    $data['dateline']  = time();
    $flag              = $db->exec('INSERT INTO ' . $bindBankTable . CreateInsertSql($data));
    if($flag){
        $res['status'] = 'success';
        $res['msg']    = '绑定成功';
    }
    return $res;
}


/**
 * 获取用户信息（其他人，不是登陆者）
 */
function getUserInfo($param){
    global $db;
    $userTab  = Table('user');
    $infoTab  = Table('user_info');
    $levelTab = Table('user_level');
    $sql     = 'SELECT u.gid, u.`username`, u.`gender`, u.`nickname`, u.`head_pic`,
                info.`qq`, info.`mobile`, info.`wechat`, info.`truename`, info.`recharge_total`, info.`level_id`, info.`last_take_prize_level`,
                l.`title`, l.`level`
                FROM ' . $userTab . ' AS u
                LEFT JOIN ' . $infoTab . ' AS info
                ON u.`id` = info.`uid`
                LEFT JOIN ' . $levelTab . ' AS l
                ON info.`level_id` = l.`id`
                WHERE u.id = ' . $param['uid'];
    $userInfo= $db->getrow($sql);
    // 获取用户的累计中奖金额
    $mainTab = Table('lottery_order_main');
    $sql     = 'SELECT SUM(prize) as cnt FROM ' . $mainTab . ' WHERE uid = ' . $param['uid'];
    $winAmount = $db->getrow($sql);
    // 获取用户常玩的彩种信息
    $itemTab  = Table('lottery_order_item');
    $lottTab  = Table('lottery');
    $cateTab  = Table('lottery_category');
    $sql      = 'SELECT cate.tags
                FROM ' . $itemTab . ' AS item
                LEFT JOIN ' . $mainTab . ' AS main
                ON item.`o_id` = main.`id`
                LEFT JOIN ' . $lottTab . ' AS lott
                ON item.`lott_id` = lott.`id`
                LEFT JOIN ' . $cateTab . ' as cate
                ON lott.cate_id = cate.id
                WHERE main.`uid` = ' . $param['uid'] . ' AND item.`lott_id` != "" GROUP BY lott.cate_id';
    $oftenPlay = $db->getall($sql);
    $oftenPlayTags  = array();
    foreach ($oftenPlay as $k => $v){
        array_push($oftenPlayTags, $v['tags']);
    }
    // 查询所有大彩种
    $sql               = 'SELECT id, name, tags, sname FROM ' . $cateTab;
    $allCate           = $db->getall($sql);
    $res['userInfo']   = $userInfo;
    $res['winAmount']  = $winAmount;
    $res['oftenPlay']  = $oftenPlayTags;
    $res['allCate']    = $allCate;
    return $res;
}

/**
 * 获取彩种期号信息
 */
function getPerInfo($param){
    if (!$param['pid']) return;
    global $db;
    $sql = 'SELECT tid, period, starttime, stoptime, `day`, opentime, `code`, `status`, createtime FROM ' . Table('lottery_k3') . ' WHERE id = ' . $param['pid'];
    $perInfo = $db->getrow($sql);
    return array('perInfo' => $perInfo);
}

/**
 * 用户创建邀请码信息
 */
function createRepointOdds($param){
    $res      = array(
        'status' => 'success',
        'msg'    => '操作成功',
    );
    global $db;
    // 生成一个code
    $mainTab       = Table('user_invita_code');
    $invitaItemTab = Table('user_invita_item');
    $p1            = date('y');
    $p2            = $_SESSION['uid'];
    $p3            = substr(microtime(true), strpos(microtime(true), '.') + 1);
    $p4            = date('m');
    $p5            = date('d');
    $code          = $p4 . $p3 . $p1 . str_pad($p2, 4, 0, STR_PAD_LEFT) . $p5;
    $mainData = array(
        'uid'         => $p2,     // 用户关系
        'invita_code' => $code,   // 邀请码
        'remark'      => '',
        'is_once'     => '1',
        'type'        => $param['type'],
        'createtime'  => time()
    );
    $main_id     = $db->add('user_invita_code', $mainData);
    if (!$main_id) {
        $res['status'] = 'error';
        $res['msg']    = '网络错误';
        return $res;
    }
    
    foreach ((array)$param['rps'] as $k => $v){
        $ins = array(
            'code_id'      => $main_id,  // 邀请码表的id
            'lott_cate_id' => $k,        // 对应彩种类别
            're_point'     => $v,        // 设置的返点数额
        );
        $itemSql  = 'INSERT INTO ' . $invitaItemTab . ' SET ' . CreateUpdateSql($ins);
        $db->exec($itemSql);
    }
    return $res;
}

/**
 * 获取用户的邀请码信息
 */
function getRpsByUid($uid, $type=''){
    global $db;
    $where      = 'inv.`uid` = ' . $uid . ' AND inv.`status` = 1';
    if ($type){
        $where .= ' AND inv.`type` = ' . $type;
    }
    $sql = 'SELECT inv.`id`, inv.`invita_code`, inv.`remark`, inv.`createtime`,
            	item.`lott_cate_id`, item.`re_point`, cate.`name`
            FROM ' . Table('user_invita_item') . ' AS item
            LEFT JOIN ' . Table('user_invita_code') . ' AS inv
            ON item.`code_id` = inv.`id`
            LEFT JOIN ' . Table('lottery_category') . ' AS cate
            ON cate.`id` = item.`lott_cate_id`
            WHERE ' . $where;
    
    $invitaData  = $db->getall($sql);
    // 整理数据，详细项的设置规整到邀请码下
    $invWithItem = array();
    foreach ($invitaData as $k => $v){
        if (!$invWithItem[$v['id']]['id'])
            $invWithItem[$v['id']]['id']            = $v['id'];
        if (!$invWithItem[$v['id']]['invita_code'])
            $invWithItem[$v['id']]['invita_code']   = $v['invita_code'];
        if (!$invWithItem[$v['id']]['createtime'])
            $invWithItem[$v['id']]['re_createtime'] = date('Y-m-d H:i:s', $v['createtime']);
        if (!$invWithItem[$v['id']]['remark'])
            $invWithItem[$v['id']]['remark']        = $v['remark'];
        $invWithItem[$v['id']]['item'][] = $v;
    }
    return $invWithItem;
}

/**
 * 用户删除自己的邀请码（其实是关闭）
 */
function delRpsById($param){
    global $db;
    $res      = array(
        'status' => 'success',
        'msg'    => '操作成功',
    );
    if (!$param['uid'] || !$param['delid']) {
        $res['status'] = 'error';
        $res['msg']    = '操作信息缺失，请重试';
        return $res;
    }
    // 只能关闭自己的邀请码
    $sql  = 'UPDATE ' . Table('user_invita_code') . ' SET status = 2 WHERE uid = ' . $param['uid'] . ' AND id = ' . $param['delid'];
    $flag = $db->exec($sql);
    if (!$flag){
        $res['status'] = 'error';
        $res['msg']    = '操作失败，请重试';
    }
    return $res;
}

/**
 * 获取用户的邀请码信息
 * @param array $param
 */
function getInvCodeList($param){
    global $db;
    if(!$param['uid']) return;
    $where = ' WHERE 1 AND cd.`uid` = ' . $param['uid'] . ' AND cd.`status` = 1';
    if ($param['type']){
        $where .= ' AND cd.`type` = ' . $param['type'];
    }
    $invcodeTab  = Table('user_invita_code');
    $invitemTab  = Table('user_invita_item');
    $lottcateTab = Table('lottery_category');
    $userinfoTab = Table('user_info');
    $sql = 'SELECT cd.`id`, cd.`invita_code`, cd.`remark`, cd.`type`, cd.`createtime`, item.`lott_cate_id`, item.`re_point` 
            FROM ' . $invcodeTab . ' AS cd 
            LEFT JOIN ' . $invitemTab . ' AS item
            ON cd.`id` = item.`code_id` 
            ';
    
    $codeData = $db->getall($sql . $where);
    // 数据格式处理，以便给调用地方直接使用
    $codeWithItemData = array();
    foreach ($codeData as $k => $v){
        if (!$codeWithItemData[$v['id']]['id']){
            $codeWithItemData[$v['id']]['id'] = $v['id'];
        }
        if (!$codeWithItemData[$v['id']]['invita_code']){
            $codeWithItemData[$v['id']]['invita_code'] = $v['invita_code'];
        }
        if (!$codeWithItemData[$v['id']]['remark']){
            $codeWithItemData[$v['id']]['remark'] = $v['remark'];
        }
        if (!$codeWithItemData[$v['id']]['type']){
            $codeWithItemData[$v['id']]['type'] = $v['type'];
        }
        if (!$codeWithItemData[$v['id']]['re_createtime']){
            $codeWithItemData[$v['id']]['re_createtime'] = date('Y-m-d H:i:s', $v['createtime']);
        }
        $codeWithItemData[$v['id']]['item'][$v['lott_cate_id']] = $v;
    }
    // 查询所有彩种类别， 不进行多连接查询
    $sql       = 'SELECT id, name FROM ' . $lottcateTab . ' WHERE status = 0';
    $cateIdKey = $db->getall($sql, 'id');
    // 查询邀请码的注册人数信息
    $codeIdStr = implode(',', array_keys($codeWithItemData));
    $sql       = 'SELECT COUNT(uid) AS code_nums, invita_code_id FROM ' . $userinfoTab . ' GROUP BY invita_code_id';
    $codeRegData = $db->getall($sql, 'invita_code_id');
    return array('cwid' => $codeWithItemData, 'cateIdKey' => $cateIdKey, 'codeRegData' => $codeRegData);
}

/**
 * 获取代理的报表统计信息
 * @param array $param
 */
function getAgentReport($param){
    global $db;
    $userTab       = Table('user');
    $userinfoTab   = Table('user_info');
    $moneylogTab   = Table('user_money_log');
    if ($param['forUser']){
        // 代理搜索账户
        $sql   = 'SELECT u.id, info.user_type 
                    FROM ' . $userTab . ' AS u
                    LEFT JOIN ' . $userinfoTab . ' AS info
                    ON u.`id` = info.`uid`
                    WHERE u.`username` = "' . $param['forUser'] . '"';
        $user  = $db->getrow($sql);
        // 未找到用户
        if (!$user) return;
        if ($user['user_type'] == 2){
            // 如果是玩家， 则直接返回
            return array('status' => 'error', 'msg' => '目标用户不是代理');
        }
        $param['agentUserLine'] = array();
        getUserLine($user['id'], $param['agentUserLine']);
    }
    $uidStr        = implode(',', array_keys($param['agentUserLine']));
    $mainTab       = Table('lottery_order_main');
    // 注册人数
    $where     = ' WHERE id IN(' . $uidStr . ') ';
    $logWhere  = ' WHERE uid IN(' . $uidStr . ') ';
    $orderWhere= ' WHERE uid IN(' . $uidStr . ') ';
    if ($param['timeLine'] == "1"){
        // 昨日
        $where      .= ' AND reg_time > ' . strtotime(date('Ymd', strtotime('-1 day'))) . ' AND reg_time < ' . strtotime(date('Ymd'));
        $logWhere   .= ' AND dateline > ' . strtotime(date('Ymd', strtotime('-1 day'))) . ' AND dateline < ' . strtotime(date('Ymd'));
        $orderWhere .= ' AND createtime > ' . strtotime(date('Ymd', strtotime('-1 day'))) . ' AND createtime < ' . strtotime(date('Ymd'));
    }elseif ($param['timeLine'] == "2"){
        // 本月
        $where      .= ' AND reg_time > ' . strtotime(date('Ym') . '01');
        $logWhere   .= ' AND dateline > ' . strtotime(date('Ym') . '01');
        $orderWhere .= ' AND createtime > ' . strtotime(date('Ym') . '01');
    }elseif ($param['timeLine'] == "3"){
        // 上个月
        $where      .= ' AND reg_time > ' . strtotime(date('Ym', strtotime('-1 month')) . '01') . ' AND reg_time < ' . strtotime(date('Ym') . '01');
        $logWhere   .= ' AND dateline > ' . strtotime(date('Ym', strtotime('-1 month')) . '01') . ' AND dateline < ' . strtotime(date('Ym') . '01');
        $orderWhere .= ' AND createtime > ' . strtotime(date('Ym', strtotime('-1 month')) . '01') . ' AND createtime < ' . strtotime(date('Ym') . '01');
    }else{
        // 今天
        $where      .= ' AND reg_time > ' . strtotime(date('Ymd'));
        $logWhere   .= ' AND dateline > ' . strtotime(date('Ymd'));
        $orderWhere .= ' AND createtime > ' . strtotime(date('Ymd'));
    }
    // 查询uidStr的所有订单信息。
    $sql           = 'SELECT uid, amount, prize FROM ' . $mainTab . $orderWhere;
    $orderData     = $db->getall($sql);
    $betAmount     = array_sum(array_column($orderData, 'amount'));
    $prizeAmount   = array_sum(array_column($orderData, 'prize'));
    
    $betUserNums   = count(array_unique(array_column($orderData, 'uid')));
    
    $sql = 'SELECT money FROM ' . $userTab . $where;
    $regUserData   = $db->getrow($sql);
    $regUserNums   = count($regUserData);
    $userMoneyAmount = array_sum(array_column($regUserData, 'money'));
    $lower_nums    = count($param['agentUserLine']);
    // 查询金额日志
    $sql     = 'SELECT tid, money FROM ' . $moneylogTab . $logWhere;
    $logData = $db->getall($sql);
    $logDataIdKey = array();
    foreach ($logData as $k => $v){
        $logDataIdKey['tid'][] = $v;
    }
    // 总返点金额
    $repointAmount = '0.00';
    if ($logDataIdKey['repoint']){
        $repointAmount  = array_sum(array_column($logDataIdKey['repoint'], 'money'));
    }
    // 总提现金额
    $cashAmount    = '0.00';
    if ($logDataIdKey['cash']) {
        $cashAmount     = array_sum(array_column($logDataIdKey['cash'], 'money'));
    }
    // 充值信息
    $rechargeAmount= '0.00';
    if ($logDataIdKey['recharge']){
        $rechargeAmount = array_sum(array_column($logDataIdKey['recharge'], 'money'));
    }
    return array('betAmount' => $betAmount, 
        'prizeAmount'     => $prizeAmount, 
        'betUserNums'     => $betUserNums, 
        'regUserNums'     => $regUserNums, 
        'userMoneyAmount' => $userMoneyAmount,
        'lower_nums'      => $lower_nums,
        'repointAmount'   => $repointAmount,
        'cashAmount'      => $cashAmount,
        'rechargeAmount'  => $rechargeAmount,
    );
}














